//
//  FMDBDepartManager.m
//  NursingManage
//
//  Created by Zhen Huang on 17/5/16.
//  Copyright © 2017年 James. All rights reserved.
//

#import "FMDBDepartManager.h"
#import "FMDatabaseAdditions.h"
#import "HDepartModel.h"
#import <TMCache.h>
#import "HMailListModel.h"

static FMDBDepartManager *manager = nil;

@implementation FMDBDepartManager

//实现单例方法
+(FMDBDepartManager *)sharedFMDBManeger {
    if (manager == nil) {
        manager = [[FMDBDepartManager alloc]init];
    }
    return manager;
}

//准备好文件路径
- (NSString *)backFilePath {
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
    NSString *newFilePath = [documentPath stringByAppendingPathComponent:@"HDepart.sqlite"];
    return newFilePath;//将新文件路径返回；
}

#pragma 创建科室排班

//创建数据库
- (void)dataDepartmentBase {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
//        NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

//创建表
- (void)createDepartmentTable {
    [self.db open];
    NSString *sqlString = @"create table if not exists 'HDepart'('emp_id'text,'jub_num'text,'emp_name'text,'group_id'text,'week_begin'text,'week_end'text)";
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    [self.db close];
    
}

//添加数据
- (void)insertHDepart:(HDepartModel *)HDepartModel{
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  HDepart (emp_id,jub_num,emp_name,group_id,week_begin,week_end)values (?,?,?,?,?,?)",HDepartModel.EMP_ID,HDepartModel.JOB_NUM,HDepartModel.EMP_NAME,HDepartModel.GROUP_ID,HDepartModel.WEEK_BEGIN,HDepartModel.WEEK_END];
    NSLog(result ? @"添加成功" :@"添加失败");
    [self.db close];
}

//查询数据 相当于数据读取操作
- (NSMutableArray *)DepartmentModel :(NSString *)string
                                    :(NSString *)week_begin
                                    :(NSString *)week_end  {
    
    
    //执行查询语句，得到查询结果
    NSString *sqlStr = [NSString stringWithFormat:@"select * from HDepart where group_id='%@' AND week_begin>='%@' AND week_end<='%@'",string,week_begin,week_end];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HDepartModel *model = [[HDepartModel alloc]init];
        model.EMP_NAME = [set stringForColumn:@"emp_name"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        model.JOB_NUM = [set stringForColumn:@"jub_num"];
        model.GROUP_ID = [set stringForColumn:@"group_id"];
        model.WEEK_BEGIN = [set stringForColumn:@"week_begin"];
        model.WEEK_END = [set stringForColumn:@"week_end"];
        [array addObject:model];
    }
    [self.db close];
    return array;
}
//删除数据
- (void)deleteHDepart:(HDepartModel *)HDepartModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"delete from HDepart"];
    NSLog(result ? @"删除成功":@"删除失败");
    
    [self.db close];
}


#pragma 创建通讯录

//创建数据库
- (void)dataAllContactsBase {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
//        NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

//创建表
- (void)createAllContactsTable {
    [self.db open];
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists 'mailList'('emp_id'text,'empt_name'text,'mobile_phone'text,'jub_num'text,'address'text,'dept_name'text,'input_code1'text,'detp_id1'text)";
    //2.执行语句
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    
    [self.db close];
    
}

//添加数据
- (void)insertAllContacts:(HMailListModel *)AllContactsModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  mailList (emp_id,empt_name,mobile_phone,jub_num,address,dept_name,input_code1,detp_id1)values (?,?,?,?,?,?,?,?)",AllContactsModel.EMP_ID,AllContactsModel.EMP_NAME,AllContactsModel.MOBILE_PHONE,AllContactsModel.JUB_NUM,AllContactsModel.ADDRESS,AllContactsModel.DEPT_NAME,AllContactsModel.INPUT_CODE1,AllContactsModel.DEPT_ID1];
    
    NSLog(result ? @"添加成功" :@"添加失败");
    
    [self.db close];
}

//查询数据 相当于数据读取操作
- (NSMutableArray *)AllContacts {
    NSMutableArray *array = [NSMutableArray new];
    array = [self AllContactsWithInputCode:@""];
    return array;
    
}
- (NSMutableArray *)AllContactsWithInputCode:(NSString *)input_code {
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    [self.db open];
    
    //执行查询语句，得到查询结果
    
    NSString* sql = @"select * from mailList where 1 = 1 %@";
    
    NSString *where = @"";
    
    if (![input_code  isEqual: @""]){
         where = [NSString stringWithFormat:@" and input_code1 like '%@%@'",input_code,@"%"];
    }
    sql = [NSString stringWithFormat:sql,where];
    
    NSString *order = @" %@ order by input_code1 asc";

    sql = [NSString stringWithFormat:order,sql];
    
    
    FMResultSet *set = [self.db executeQuery:sql];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HMailListModel *model = [[HMailListModel alloc]init];
        model.EMP_NAME = [set stringForColumn:@"empt_name"];
        model.JUB_NUM = [set stringForColumn:@"jub_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        model.DEPT_NAME = [set stringForColumn:@"dept_name"];
        model.ADDRESS = [set stringForColumn:@"address"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        model.MOBILE_PHONE = [set stringForColumn:@"mobile_phone"];
        model.DEPT_ID1 = [set stringForColumn:@"detp_id1"];
        [array addObject:model];
        
    }
    [self.db close];
    return array;
}
- (void)deleteAllContacts:(HMailListModel *)AllContactsModel
{
    [self.db open];
    BOOL result = [self.db executeUpdate:@"delete from mailList"];
    NSLog(result ? @"删除成功":@"删除失败");
    [self.db close];

}



#pragma 创建科室权限


//创建数据库
- (void)dataJurisdictionBase {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
//        NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

//创建表
- (void)createJurisdictionTable {
    [self.db open];
    NSString *sqlString = @"create table if not exists 'HJD'('group_name'text,'group_id'text)";
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    [self.db close];
    
}

//添加数据
- (void)insertJurisdictionModel:(HJurisdictionModel *)HJDModel{
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  HJD (group_name,group_id)values (?,?)",HJDModel.GROUP_NAME,HJDModel.GROUP_ID];
    NSLog(result ? @"添加成功" :@"添加失败");
    [self.db close];
}

//查询数据 相当于数据读取操作
- (NSMutableArray *)Jurisdiction {
    //执行查询语句，得到查询结果
    //    NSString *emp_id = [[TMCache sharedCache]objectForKey:@"emp_id"];
    //    NSString *sqlStr = [NSString stringWithFormat:@"select * from HSCDList where emp_id='%@'",emp_id];
    FMResultSet *set = [self.db executeQuery:@"select * from HJD"];
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HJurisdictionModel *model = [[HJurisdictionModel alloc]init];
        model.GROUP_NAME = [set stringForColumn:@"group_name"];
        model.GROUP_ID = [set stringForColumn:@"group_id"];
        [array addObject:model];
    }
    [self.db close];
    return array;
}


//删除数据
- (void)deleteHJD:(HJurisdictionModel *)HJDModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"delete from HJD"];
    NSLog(result ? @"删除成功":@"删除失败");
    
    [self.db close];
}



//创建数据库
- (void)dataBaseSCD {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
//        NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

#pragma 创建个人排班

//创建表
- (void)createTableSCD {
    [self.db open];
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists 'HSCDList'('scd_group_id'text,'emp_id'text,'scd_date'text,'scd_class'text,'class_name'text)";
    //2.执行语句
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    
    [self.db close];
    
}

//添加数据
- (void)insertSCD:(HSCDModel *)MailModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  HSCDList (scd_group_id,emp_id,scd_date,scd_class,class_name)values (?,?,?,?,?)",MailModel.SCD_GROUP_ID,MailModel.EMP_ID,MailModel.SCD_DATE,MailModel.SCD_CLASS,MailModel.CLASS_NAME];
    NSLog(result ? @"添加成功" :@"添加失败");
    [self.db close];
} 

//查询数据 相当于数据读取操作
- (NSMutableArray *)allModelSCD :(NSString *)emp_id{
    //执行查询语句，得到查询结果
    NSString *sqlStr = [NSString stringWithFormat:@"select * from HSCDList where emp_id='%@'",emp_id];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HSCDModel *model = [[HSCDModel alloc]init];
        model.SCD_CLASS = [set stringForColumn:@"scd_class"];
        model.SCD_DATE = [set stringForColumn:@"scd_date"];
        model.SCD_GROUP_ID = [set stringForColumn:@"scd_group_id"];
        model.CLASS_NAME = [set stringForColumn:@"class_name"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        [array addObject:model];
        
    }
    [self.db close];
    return array;
}
- (NSMutableArray *)allModelSCDWithdate :(NSString *)emp_id
                                SCD_date:(NSString *)SCD_date
                             SCD_EndDATE:(NSString *)SCD_EndDate
{
    
//执行查询语句，得到查询结果
    NSString *sqlStr = [NSString stringWithFormat:@"select * from HSCDList where scd_group_id ='%@' and scd_date >= '%@' and scd_date <= '%@' ",emp_id,SCD_date,SCD_EndDate];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HSCDModel *model = [[HSCDModel alloc]init];
        model.SCD_CLASS = [set stringForColumn:@"scd_class"];
        model.SCD_DATE = [set stringForColumn:@"scd_date"];
        model.SCD_GROUP_ID = [set stringForColumn:@"scd_group_id"];
        model.CLASS_NAME = [set stringForColumn:@"class_name"];
        model.EMP_ID = [set stringForColumn:@"emp_id"];
        [array addObject:model];
        
    }
    [self.db close];
    return array;
}




//删除数据
- (void)deleteSCD:(HSCDModel *)MailModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"delete from HSCDList"];
    NSLog(result ? @"删除成功":@"删除失败");
    
    [self.db close];
}

#pragma 创建qc_type

- (void)dataQc_type {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
        //NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

//创建表
- (void)createQc_typeTable {
    [self.db open];
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists 'HQc_Type'('type_id'text,'type_name'text,'order_num'text,'input_code1'text)";
    //2.执行语句
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    
    [self.db close];
    
}

//添加数据
- (void)insertQc_type:(HQcTypeModel *)HQcTypeModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  HQc_Type (type_id,type_name,order_num,input_code1)values (?,?,?,?)",HQcTypeModel.TYPE_ID,HQcTypeModel.TYPE_NAME,HQcTypeModel.ORDER_NUM,HQcTypeModel.INPUT_CODE1];
    NSLog(result ? @"添加成功" :@"添加失败");
    [self.db close];
}

- (NSMutableArray *)Qc_type
{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from HQc_Type "];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQcTypeModel *model = [[HQcTypeModel alloc]init];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.TYPE_NAME = [set stringForColumn:@"type_name"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        [array addObject:model];
        
    }
    [self.db close];
    return array;

}
- (NSInteger)QC_TypeCount
{
    int resules=[self.db intForQuery:@"select count(*) from HQc_Type"];
    return resules;
    
}

#pragma 创建qc_standard
- (void)dataQc_standard {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
        //NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

//创建表
- (void)createQc_standardTable {
    [self.db open];
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists 'HQc_Standard'('standard_id'text,'standard_name'text,'order_num'text,'type_id'text,'input_code1'text)";
    //2.执行语句
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    
    [self.db close];
    
}
//添加数据
- (void)insertQc_standard:(HQc_standardModel *)HQcStandardModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  HQc_Standard (standard_id,standard_name,order_num,type_id,input_code1)values (?,?,?,?,?)",HQcStandardModel.STANDARD_ID,HQcStandardModel.STANDARD_NAME,HQcStandardModel.ORDER_NUM,HQcStandardModel.TYPE_ID,HQcStandardModel.INPUT_CODE1];
    NSLog(result ? @"添加成功" :@"添加失败");
    [self.db close];
}

- (NSMutableArray *)Qc_standard:(NSString *)Type_id
{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from HQc_Standard where type_id ='%@'",Type_id];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_standardModel *model = [[HQc_standardModel alloc]init];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.STANDARD_NAME = [set stringForColumn:@"standard_name"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        model.INPUT_CODE1 = [set stringForColumn:@"input_code1"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        [array addObject:model];
        
    }
    [self.db close];
    return array;
}


#pragma 创建qc_code
- (void)dataQc_code {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
        //NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

//创建表
- (void)createQc_codeTable {
    [self.db open];
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists 'HQc_Code'('code_id'text,'type_id'text,'standard_id'text,'code_name'text,'par_id'text,'end_flag'text,'levels'text,'score'text,'standard_score'text,'weights'text,'inspection_method'text,'score_method'text,'otherhelp'text,''order_num'text)";
    //2.执行语句
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    
    [self.db close];
    
}
//添加数据
- (void)insertQc_code:(HQc_codeModel *)HQc_codeModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  HQc_Code (code_id,type_id,standard_id,code_name,par_id,end_flag,levels,score,standard_score,weights,inspection_method,score_method,otherhelp,order_num)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",HQc_codeModel.CODE_ID,HQc_codeModel.TYPE_ID,HQc_codeModel.STANDARD_ID,HQc_codeModel.CODE_NAME,HQc_codeModel.PAR_ID,HQc_codeModel.END_FLAG,HQc_codeModel.LEVELS,HQc_codeModel.SCORE,HQc_codeModel.STANDARD_SCORE,HQc_codeModel.WEIGHTS,HQc_codeModel.INSPECTION_METHOD,HQc_codeModel.SCORE_METHOD,HQc_codeModel.OTHERHELP,HQc_codeModel.ORDER_NUM];
    NSLog(result ? @"添加成功" :@"添加失败");
    [self.db close];
}

- (NSMutableArray *)Qc_code:(NSString *)emp_id
{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from HQc_Code where type_id ='%@'",emp_id];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_codeModel *model = [[HQc_codeModel alloc]init];
        model.CODE_ID = [set stringForColumn:@"code_id"];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.CODE_NAME = [set stringForColumn:@"code_name"];
        model.PAR_ID = [set stringForColumn:@"par_id"];
        model.END_FLAG = [set stringForColumn:@"end_flag"];
        model.LEVELS = [set stringForColumn:@"levels"];
        model.SCORE = [set stringForColumn:@"score"];
        model.STANDARD_SCORE = [set stringForColumn:@"standard_score"];
        model.WEIGHTS = [set stringForColumn:@"weights"];
        model.INSPECTION_METHOD = [set stringForColumn:@"inspection_method"];
        model.SCORE_METHOD = [set stringForColumn:@"score_method"];
        model.OTHERHELP = [set stringForColumn:@"otherhelp"];
        model.ORDER_NUM = [set stringForColumn:@"order_num"];
        [array addObject:model];
        
    }
    [self.db close];
    return array;
}




#pragma 创建qc_dept
- (void)dataQc_dept {
    //创建数据库对象
    self.db = [FMDatabase databaseWithPath:[self backFilePath]];
    //打开数据库
    if ([self.db open]) {
        //NSLog(@"数据库打开成功");
    }else {
        NSLog(@"数据库打开失败");
    }
}

//创建表
- (void)createQc_deptTable {
    [self.db open];
    //1.准备sql语句
    NSString *sqlString = @"create table if not exists 'HQc_dept'('type_id'text,'standard_id'text,'depart_id'text,'dept_name'text)";
    //2.执行语句
    BOOL result = [self.db executeUpdate:sqlString];
    NSLog(result ? @"创建成功":@"创建失败");
    
    [self.db close];
    
}
//添加数据
- (void)insertQc_dept:(HQc_deptModel *)HQc_deptModel {
    [self.db open];
    BOOL result = [self.db executeUpdate:@"insert into  HQc_dept (type_id,standard_id,depart_id,dept_name)values (?,?,?,?)",HQc_deptModel.TYPE_ID,HQc_deptModel.STANDARD_ID,HQc_deptModel.DEPART_ID,HQc_deptModel.DEPT_NAME];
    NSLog(result ? @"添加成功" :@"添加失败");
    [self.db close];
}

- (NSMutableArray *)Qc_dept:(NSString *)emp_id
{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from HQc_Standard where type_id ='%@'",emp_id];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    //上述方法就是将数据去全部获取放进集合
    NSMutableArray *array = [NSMutableArray array];
    while ([set next]) {
        HQc_deptModel *model = [[HQc_deptModel alloc]init];
        model.TYPE_ID = [set stringForColumn:@"type_id"];
        model.STANDARD_ID = [set stringForColumn:@"standard_id"];
        model.DEPT_NAME = [set stringForColumn:@"depart_id"];
        model.DEPART_ID = [set stringForColumn:@"dept_name"];
        [array addObject:model];
    }
    [self.db close];
    return array;
}







@end
